﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_MailGetBriefInfo]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_MailGetBriefInfo];
GO
-------------------------------------------------------------------------------
-- sproc_MailGetBriefIInboxnfo
-------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[sproc_MailGetBriefInfo]
(
    @Username             char(20),
    @MailFolderType     int     
)
AS
--得到某用户某收件夹中的邮件简要信息
--1    收件
--2    我的发件
--3    我的回收
if @MailFolderType=1
    SELECT MailID,ClassID,(SELECT ClassName FROM UDS_Class a WHERE a.ClassID=TabMailList.ClassID) as ClassName,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailSender) as MailSender,(SELECT RealName  FROM UDS_Staff a  where a.Staff_Name=MailReceiver)  as MailReceiver,convert(nvarchar(10),TabMailList.MailSendDate,120) as MailSendDate,MailSubject,MailReadFlag,MailReceiverStr,(select count(*) from TabMailAttachFiles where MailID=TabMailList.MailID) AS attnumber,(select sum(FileSize)/1024 from TabMailAttachFiles where MailID=TabMailList.MailID) AS attsize
    FROM TabMailList
    WHERE    MailReceiver = @Username AND MailFolderType = @MailFolderType  order by MailID DESC
if @MailFolderType=2
    SELECT MailID,ClassID,(SELECT ClassName FROM UDS_Class a WHERE a.ClassID=TabMailList.ClassID) as ClassName,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailSender) as MailSender,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailReceiver)  as MailReceiver,convert(nvarchar(10),TabMailList.MailSendDate,120) as MailSendDate,MailSubject,MailReadFlag,MailReceiverStr,(select count(*) from TabMailAttachFiles where MailID=TabMailList.MailID) AS attnumber ,(select sum(FileSize)/1024 from TabMailAttachFiles where MailID=TabMailList.MailID) AS attsize
    FROM TabMailList
    WHERE    MailSender = @Username AND MailFolderType = @MailFolderType   order by MailID DESC
if @MailFolderType=3
     SELECT MailID,ClassID,(SELECT ClassName FROM UDS_Class a WHERE a.ClassID=TabMailList.ClassID) as ClassName,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailSender) as MailSender,(SELECT RealName FROM UDS_Staff a where a.Staff_Name=MailReceiver)  as MailReceiver,convert(nvarchar(10),TabMailList.MailSendDate,120) as MailSendDate,MailSubject,MailReadFlag,MailReceiverStr,(select count(*) from TabMailAttachFiles where MailID=TabMailList.MailID) AS attnumber ,(select sum(FileSize)/1024 from TabMailAttachFiles where MailID=TabMailList.MailID) AS attsize
    FROM TabMailList
    WHERE    (MailReceiver = @Username  or MailSender = @Username) AND MailFolderType = @MailFolderType order by MailID DESC
if @MailFolderType=4
    SELECT MailID,Subject as MailSubject,Username,Email,ReadFlag as MailReadFlag,Username AS MailReceiver,Username As  MailReceiverStr,'' As ClassName,BodySize,ReplyTo As MailSender,SendDate As    MailSendDate,0 as attnumber,0 as attsize
   FROM TabExtMailList
  WHERE Username = @Username